package org.compiere.model;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Properties;
import java.util.logging.Level;

import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;

public class Mhrmpaymentlist extends X_hrm_payment_list {
	
	/**
	 * 
	 */
	private static final long serialVersionUID = -2315688526714730631L;
	
	/**	Static Logger	*/
	private static CLogger	s_log	= CLogger.getCLogger (Mhrmpaymentlist.class);

	public Mhrmpaymentlist(Properties ctx, int hrm_payment_list_ID,
			String trxName) {
		super(ctx, hrm_payment_list_ID, trxName);
	}

	public Mhrmpaymentlist(Properties ctx, ResultSet rs, String trxName) {
		super(ctx, rs, trxName);
	}

	public static Mhrmpaymentlist getPaymentListRec(int C_BPartner_ID, Timestamp dt) {
		
		//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		Mhrmpaymentlist hrmpa = new Mhrmpaymentlist(Env.getCtx(), 0, null);
			
		// Employee data from the card
			String sql_ = "SELECT * FROM HRM_PAYMENT_LIST \n " +
							"WHERE ISACTIVE='Y' \n "  +
								"AND AD_CLIENT_ID="  + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
								"AND C_BPARTNER_ID=" + C_BPartner_ID                     + " \n " +
								" AND DATEFROM BETWEEN DATE_TRUNC('MONTH', "+DB.TO_DATE(dt, true)+") " +
								" AND DATE_TRUNC('MONTH',"+DB.TO_DATE(dt, true)+") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL" ;
			try
			{
				pstmt = DB.prepareStatement(sql_,null);								
				rs = pstmt.executeQuery();	
				if(rs.next())
					hrmpa = new Mhrmpaymentlist(Env.getCtx(), rs, null);
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}	
		return hrmpa;
		
	}
	
public static Mhrmpaymentlist[] getPaymentArray(int C_BPartner_ID, Timestamp sdt, Timestamp edt) {
		
		//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		Mhrmpaymentlist hrmpl = null;
			
		ArrayList<Mhrmpaymentlist> list = new ArrayList<Mhrmpaymentlist>();
		// 
			String sql_ = "SELECT *	FROM HRM_PAYMENT_LIST \n " +
							"WHERE ISACTIVE = 'Y' \n " +
								"AND AD_CLIENT_ID ="  + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
								"AND C_BPARTNER_ID="  + C_BPartner_ID                     + " \n " +
								"AND DATEFROM >=" + DB.TO_DATE(sdt) + " \n " +
								"AND DATEFROM <=" + DB.TO_DATE(edt) + " \n " ;
			try
			{
				pstmt = DB.prepareStatement(sql_,null);								
				rs = pstmt.executeQuery();	
				while(rs.next()){
					hrmpl = new  Mhrmpaymentlist(Env.getCtx(), rs, null);
					list.add(hrmpl);
				}
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}	
			
			return list.toArray(new Mhrmpaymentlist[list.size()]);		
	}

public static Mhrmpaymentlist[] getCount(Timestamp dt) {
	//
    PreparedStatement pstmt = null;
	ResultSet rs = null;
	Mhrmpaymentlist result = null;
	
	ArrayList<Mhrmpaymentlist> list = new ArrayList<Mhrmpaymentlist>();
					
	// 
	String sql_ = "SELECT * FROM HRM_PAYMENT_LIST \n" +
					" WHERE AD_CLIENT_ID="  + Env.getAD_Client_ID(Env.getCtx()) + " \n" +
						" AND DATEFROM BETWEEN DATE_TRUNC('MONTH', " + DB.TO_DATE(dt, true) + ") " +
						" AND DATE_TRUNC('MONTH'," + DB.TO_DATE(dt, true) + ") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL";
		try
		{
			pstmt = DB.prepareStatement(sql_,null);
			rs = pstmt.executeQuery();	
			while(rs.next()){
				result = new Mhrmpaymentlist(Env.getCtx(), rs, null);
				list.add(result);						
			}						
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		
	return list.toArray(new Mhrmpaymentlist[list.size()]);
	}
}

